#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
============================================================================
    Desc: 过滤出监控日报中cpu、内存资源使用大于70%的服务器
    Author: zhoubangjun
    Date: 2024/6/6 
=============================================================================
"""
import os
import logging
import sys

import pandas as pd
from openpyxl import load_workbook
import datetime

logging.basicConfig(level=logging.INFO)
base_dir = os.path.dirname(os.path.abspath(__file__))

server_ledger_path = os.path.join(base_dir, '技术平台台账.xlsx')
server_inspetction_archive_dir = os.path.join(base_dir, '跨模型监控报表日报')
server_usage_gt_70_archive_dir = os.path.join(base_dir, 'archive')
os.makedirs(server_inspetction_archive_dir, exist_ok=True)
os.makedirs(server_usage_gt_70_archive_dir, exist_ok=True)

if not os.path.exists(server_ledger_path):
    logging.error(f'服务器台账文件: {server_ledger_path}不存在')
    exit(1)

def get_latest_file(archive_dir):
    files = os.listdir(archive_dir)
    files.sort(key=lambda x: os.path.getmtime(os.path.join(archive_dir, x)))
    return files[-1]


def filter_usage_gt_70():
    today = datetime.datetime.now().date()
    previous_day = today - datetime.timedelta(days=1)
    latest_server_report_file_name = get_latest_file(server_inspetction_archive_dir)
    server_report_path = f'{server_inspetction_archive_dir}/{latest_server_report_file_name}'

    logging.info(f'latest_server_report_file_name: {latest_server_report_file_name}')
    server_usage_gt_70_report_path = f"{server_usage_gt_70_archive_dir}/{previous_day.strftime('%Y.%m.%d')}-监控报表日报-cpu或内存高于70%的服务器.xlsx"

    # engine='xlrd': supports old-style Excel files (.xls). which python > 3.8 is not supported

    if f'{sys.version_info.major}.{sys.version_info.minor}' >= '3.8':
        df_server_report = pd.read_excel(server_report_path, engine='openpyxl', skiprows=range(3))
    else:
        df_server_report = pd.read_excel(server_report_path, engine='xlrd', skiprows=range(3))
    df_server_ledger = pd.read_excel(server_ledger_path, engine='openpyxl')
    # column_name_list = df_server_report.columns.tolist()

    filtered_df = df_server_report[((df_server_report['最大值'].str.rstrip('%').astype(float) / 100) > 0.7) |
                      ((df_server_report['平均值'].str.rstrip('%').astype(float) / 100) > 0.7)]

    with pd.ExcelWriter(server_usage_gt_70_report_path, engine='openpyxl', mode='w') as writer:
        filtered_df.to_excel(writer, index=False)

    df_server_usage_gt_70_report = pd.read_excel(server_usage_gt_70_report_path)
    df_server_usage_gt_70_report.insert(df_server_usage_gt_70_report.columns.get_loc('所属业务')+1, '所属子业务', '')

    def get_sub_business(ip):
        return df_server_ledger[df_server_ledger['IP'] == ip]['所属子业务'].values[0] if ip in df_server_ledger['IP'].values else ''

    for idx, row in df_server_usage_gt_70_report.iterrows():
        df_server_usage_gt_70_report.at[idx, '所属子业务'] = get_sub_business(row['IP'])

    with pd.ExcelWriter(server_usage_gt_70_report_path, engine='openpyxl', mode='a') as writer:
        df_server_usage_gt_70_report.to_excel(writer, sheet_name='Sheet', index=False)

    wb = load_workbook(server_usage_gt_70_report_path)
    # 删除除sheet外其他sheet页
    for sheet in wb.sheetnames:
        if sheet != 'Sheet':
            wb.remove(wb[sheet])
    wb.save(server_usage_gt_70_report_path)
    logging.info(f"数据处理完成,文件位置: {server_usage_gt_70_report_path}")


if __name__ == '__main__':
    filter_usage_gt_70()
